﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    /// <summary>
    /// 访问数据库操作类
    /// </summary>
    class DBHepler
    {
        private SqlConnection myConnection;
        private SqlCommand cmd;
        private SqlDataAdapter adapter;

        public static readonly string conSql = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        /// <summary>
        /// 创建连接
        /// </summary>
        public DBHepler()
        {
            myConnection = new SqlConnection(conSql);
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        private void CloseConnection()
        {
            myConnection.Close();
            
        }

        /// <summary>
        /// 根据存储过程返回表(无参数)
        /// </summary>
        /// <param name="proc"></param>
        /// <returns></returns>
        public DataTable GetDataTableByProc(string proc)
        {
            using (DataTable dt = new DataTable())
            {
                using (adapter = new SqlDataAdapter(proc, myConnection))
                {
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adapter.Fill(dt);
                }
                return dt;
            }
        }

        /// <summary>
        /// 通过存储过程返回表(有参数)
        /// </summary>
        /// <param name="proc">存储过程</param>
        /// <param name="parameter">参数集</param>
        /// <returns></returns>
        public DataTable GetDataTableByProc(string proc, SqlParameter[] parameter)
        {
            using (DataTable dt = new DataTable())
            {
                using (adapter = new SqlDataAdapter(proc, myConnection))
                {
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adapter.SelectCommand.Parameters.AddRange(parameter);
                    adapter.Fill(dt);
                }
                return dt;
            }
        }

        /// <summary>
        /// 通过存储过程更新数据库
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public int UpdateByProc(string proc, SqlParameter[] parameter)
        {
            int result = 0;
            myConnection.Open();
            using (cmd = new SqlCommand(proc, myConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(parameter);
                result = cmd.ExecuteNonQuery();
            }
            myConnection.Close();
            return result;
        }

        /// <summary>
        /// 通过存储过程插入数据
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public int InsertByProc(string proc, SqlParameter[] parameter)
        {
            int result = 0;
            myConnection.Open();
            using (cmd = new SqlCommand(proc, myConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(parameter);
                result = cmd.ExecuteNonQuery();
            }
            myConnection.Close();
            return result;
        }

        /// <summary>
        /// 根据存储删除数据
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public int DeleteByProc(string proc, SqlParameter[] parameter)
        {
            int result = 0;
            myConnection.Open();
            using (cmd = new SqlCommand(proc, myConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(parameter);
                result = cmd.ExecuteNonQuery();
            }
            myConnection.Close();
            return result;
        }

        /// <summary>
        /// 返回指定的一条记录
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public string GetExecuteScalar(string proc, SqlParameter[] parameter)
        {
            string result = String.Empty;
            myConnection.Open();
            using (cmd = new SqlCommand(proc, myConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(parameter);
                result = cmd.ExecuteScalar().ToString();
            }
            myConnection.Close();
            return result;
        }

        public string GetExecuteScalar(string proc)
        {
            string result = String.Empty;
            myConnection.Open();
            using (cmd = new SqlCommand(proc, myConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                result = cmd.ExecuteScalar().ToString();
            }
            myConnection.Close();
            return result;
        }
    }
}
